﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
namespace Gaea
{
    public abstract partial class GaeaBasicPower
    {
        /// <summary>
        /// 构建SQL Connection
        /// </summary>
        /// <returns></returns>
        public virtual IDbConnection CreateConnection()
        {
            return _connectionFactory.GetConnection(this);
        }

        /// <summary>
        /// 显示构建 SQL Connection
        /// </summary>
        /// <typeparam name="TDbConnection">DbConnection 子类</typeparam>
        /// <returns></returns>
        public virtual TDbConnection CreateConnection<TDbConnection>() where TDbConnection : class, IDbConnection
        {
            return _connectionFactory.GetConnection(this) as TDbConnection;
        }

        /// <summary>
        /// 查询多行记录
        /// </summary>
        /// <typeparam name="T">返回结果映射类型</typeparam>
        /// <param name="commandText">T-SQL</param>
        /// <param name="parameters">参数</param>
        /// <param name="commandType">T-SQL语句类型</param>
        /// <param name="commandTimeout">执行超时时间</param>
        /// <returns>对应类型结果集</returns>
        public virtual IEnumerable<T> Find<T>(string commandText, object parameters, CommandType commandType = CommandType.Text, int? commandTimeout = default(int?)) where T : class
        {
            using (var dbConnection = CreateConnection())
            {
                return Find<T>(dbConnection, commandText, parameters, commandType, commandTimeout);
            }
        }

        /// <summary>
        /// 查询多行记录
        /// </summary>
        /// <typeparam name="T">返回结果映射类型</typeparam>
        /// <param name="commandText">T-SQL</param>
        /// <param name="parameters">参数</param>
        /// <param name="commandType">T-SQL语句类型</param>
        /// <param name="commandTimeout">执行超时时间</param>
        /// <returns>对应类型结果集</returns>
        public virtual IEnumerable<T> Find<T>(IDbConnection dbConnection, string commandText, object parameters, CommandType commandType = CommandType.Text, int? commandTimeout = null) where T : class
        {
            var result = dbConnection.Query<T>(commandText, parameters, null, commandTimeout: commandTimeout, commandType: commandType);
            dbConnection.Close();
            return result;
        }


        /// <summary>
        /// 查询单条记录（如果查询记录出现多条则抛出异常）
        /// </summary>
        /// <typeparam name="T">返回结果映射类型</typeparam>
        /// <param name="commandText">T-SQL</param>
        /// <param name="parameters">参数</param>
        /// <param name="commandType">T-SQL语句类型</param>
        /// <param name="commandTimeout">执行超时时间</param>
        /// <returns>结果</returns>
        public virtual T FindOne<T>(string commandText, object parameters, CommandType commandType = CommandType.Text, int? commandTimeout = default(int?)) where T : class
        {
            using (var dbConnection = CreateConnection())
            {
                var result = dbConnection.QuerySingleOrDefault<T>(commandText, parameters, null, commandTimeout, commandType);
                dbConnection.Close();
                return result;
            }
        }

        /// <summary>
        /// 查询单条记录（如果查询记录出现多条则抛出异常）多用于事务
        /// </summary>
        /// <typeparam name="T">返回结果映射类型</typeparam>
        /// <param name="dbConnection">Db Connection</param>
        /// <param name="commandText">T-SQL</param>
        /// <param name="parameters">参数</param>
        /// <param name="commandType">T-SQL语句类型</param>
        /// <param name="commandTimeout">执行超时时间</param>
        /// <returns>结果</returns>
        public virtual T FindOne<T>(IDbConnection dbConnection, string commandText, object parameters, CommandType commandType = CommandType.Text, int? commandTimeout = default(int?)) where T : class
        {
            var result = dbConnection.QuerySingleOrDefault<T>(commandText, parameters, null, commandTimeout, commandType);
            return result;
        }

        /// <summary>
        /// 执行SQL返回第一行第一列结果
        /// </summary>
        /// <typeparam name="T">返回结果映射类型</typeparam>
        /// <param name="commandText">T-SQL</param>
        /// <param name="parameters">参数</param>
        /// <param name="commandType">T-SQL语句类型</param>
        /// <param name="commandTimeout">执行超时时间</param>
        /// <returns>第一行第一列结果</returns>
        public virtual T ExecuteScalar<T>(string commandText, object parameters, CommandType commandType = CommandType.Text, int? commandTimeout = default(int?))
        {
            using (var dbConnection = CreateConnection())
            {
                var result = ExecuteScalar<T>(dbConnection, commandText, parameters, commandType, commandTimeout);
                dbConnection.Close();
                return result;
            }
        }

        /// <summary>
        /// 执行SQL返回第一行第一列结果 多用于事务
        /// </summary>
        /// <typeparam name="T">返回结果映射类型</typeparam>
        /// <param name="dbConnection">Db Connection</param>
        /// <param name="commandText">T-SQL</param>
        /// <param name="parameters">参数</param>
        /// <param name="commandType">T-SQL语句类型</param>
        /// <param name="commandTimeout">执行超时时间</param>
        /// <returns>第一行第一列结果</returns>
        public virtual T ExecuteScalar<T>(IDbConnection dbConnection, string commandText, object parameters, CommandType commandType = CommandType.Text, int? commandTimeout = default(int?))
        {
            return dbConnection.ExecuteScalar<T>(commandText, parameters, null, commandTimeout, commandType);
        }

        /// <summary>
        /// 执行SQL返回影响行数
        /// </summary>
        /// <param name="commandText">T-SQL</param>
        /// <param name="parameters">参数</param>
        /// <param name="commandType">T-SQL语句类型</param>
        /// <param name="commandTimeout">执行超时时间</param>
        /// <returns>影响行数</returns>
        public virtual int Execute(string commandText, object parameters, CommandType commandType = CommandType.Text, int? commandTimeout = default(int?))
        {
            using (var dbConnection = CreateConnection())
            {
                var result = Execute(dbConnection, commandText, parameters, commandType, commandTimeout);
                dbConnection.Close();
                return result;
            }
        }

        /// <summary>
        /// 执行SQL返回影响行数 多用于事务
        /// </summary>
        /// <param name="dbConnection">Db Connection</param>
        /// <param name="commandText">T-SQL</param>
        /// <param name="parameters">参数</param>
        /// <param name="commandType">T-SQL语句类型</param>
        /// <param name="commandTimeout">执行超时时间</param>
        /// <returns>影响行数</returns>
        public virtual int Execute(IDbConnection dbConnection, string commandText, object parameters, CommandType commandType = CommandType.Text, int? commandTimeout = default(int?))
        {
            return dbConnection.Execute(commandText, parameters, null, commandTimeout, commandType);
        }

        /// <summary>
        /// 返回所有结果
        /// </summary>
        /// <typeparam name="T">返回结果映射类型</typeparam>
        /// <returns>所有结果列表</returns>
        public virtual IEnumerable<T> FindAll<T>() where T : class, IGaeaSon
        {
            using (var dbConnection = CreateConnection())
            {
                var result = dbConnection.FindAll<T>(this);
                dbConnection.Close();
                return result;
            }
        }

        /// <summary>
        /// 返回所有结果
        /// </summary>
        /// <typeparam name="T">返回结果映射类型</typeparam>
        /// <param name="sort"></param>
        /// <param name="sortField"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public virtual IEnumerable<T> FindAll<T>(GaeaSort sort = GaeaSort.ASC, string sortField = "id", int? commandTimeout = default(int?)) where T : IGaeaSon
        {
            return FindAll<T>((tableName) =>
             {
                 var commandText = $"ORDER BY {sortField}";
                 if (sort == GaeaSort.DESC)
                 {
                     commandText += " DESC";
                 }
                 return commandText;
             }, commandTimeout);
        }

        /// <summary>
        /// 返回所有结果
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="buidAfterWhereCommand"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public virtual IEnumerable<T> FindAll<T>(Func<string, string> buidAfterWhereCommand, int? commandTimeout = default(int?)) where T : IGaeaSon
        {
            var tableName = CallGaeaName<T>();
            var commandText = $"SELECT * FROM {tableName} {buidAfterWhereCommand.Invoke(tableName)}";
            using (var dbConnection = CreateConnection())
            {
                var result = dbConnection.Query<T>(commandText, commandTimeout: commandTimeout);
                dbConnection.Close();
                return result;
            }
        }

        /// <summary>
        /// Id 查询出对应结果并映射
        /// </summary>
        /// <typeparam name="T">映射类型</typeparam>
        /// <param name="id">内部唯一编号</param>
        /// <param name="commandTimeout">超时时间</param>
        /// <returns>映射类型</returns>
        public virtual T FindById<T>(int id, int? commandTimeout = null) where T : class
        {
            using (var dbConnection = CreateConnection())
            {
                var result = FindById<T>(dbConnection, id, commandTimeout);
                dbConnection.Close();
                return result;
            }
        }

        /// <summary>
        ///  Id 查询出对应结果并映射 多用于事务
        /// </summary>
        /// <typeparam name="T">映射类型</typeparam>
        /// <param name="dbConnection">Db Connection</param>
        /// <param name="id">内部编号</param>
        /// <param name="commandTimeout">超时时间</param>
        /// <returns>映射类型</returns>
        public virtual T FindById<T>(IDbConnection dbConnection, int id, int? commandTimeout = null) where T : class
        {
            return dbConnection.FindById<T>(this, id);
        }

        /// <summary>
        /// 类型映射 数据插入操作
        /// </summary>
        /// <typeparam name="T">映射类型</typeparam>
        /// <param name="son">实例</param>
        /// <returns>影响行数</returns>
        public virtual int Insert<T>(T son) where T : class, IGaeaSon
        {
            using (var dbConnection = CreateConnection())
            {
                var result = Insert(dbConnection, son);
                dbConnection.Close();
                return result;
            }
        }

        /// <summary>
        /// 类型映射 数据插入操作 多用于事务
        /// </summary>
        /// <typeparam name="T">映射类型</typeparam>
        /// <param name="dbConnection">Db Connection</param>
        /// <param name="son">实例</param>
        /// <returns>影响行数</returns>
        public virtual int Insert<T>(IDbConnection dbConnection, T son) where T : class, IGaeaSon
        {
            return dbConnection.Insert(this, son);
        }

        /// <summary>
        /// 类型映射 数据库更新操作
        /// </summary>
        /// <typeparam name="T">映射类型</typeparam>
        /// <param name="son">实例</param>
        /// <returns>影响行数</returns>
        public virtual int Update<T>(T son) where T : class, IGaeaSon
        {
            using (var dbConnection = CreateConnection())
            {
                var result = Update(dbConnection, son);
                dbConnection.Close();
                return result;
            }
        }

        /// <summary>
        /// 类型映射 数据库更新操作
        /// </summary>
        /// <typeparam name="T">映射类型</typeparam>
        /// <param name="dbConnection">Db Connection</param>
        /// <param name="son">实例</param>
        /// <returns>影响行数</returns>
        public virtual int Update<T>(IDbConnection dbConnection, T son) where T : class, IGaeaSon
        {
            return dbConnection.Update(this, son);
        }

        /// <summary>
        /// 批量数据插入操作
        /// </summary>
        /// <typeparam name="T">映射类型</typeparam>
        /// <param name="commandText">T-SQL</param>
        /// <param name="sons">实例集合</param>
        /// <returns>影响行数</returns>
        public virtual int BatchInsert<T>(string commandText, IEnumerable<T> sons) where T : IGaeaSon
        {
            using (var dbConnection = CreateConnection())
            {
                var result = BatchInsert(dbConnection, commandText, sons);
                dbConnection.Close();
                return result;
            }
        }

        /// <summary>
        /// 批量数据插入操作
        /// </summary>
        /// <typeparam name="T">映射类型</typeparam>
        /// <param name="dbConnection">Db Connection</param>
        /// <param name="commandText">T-SQL</param>
        /// <param name="sons">实例集合</param>
        /// <returns>影响行数</returns>
        public virtual int BatchInsert<T>(IDbConnection dbConnection, string commandText, IEnumerable<T> sons) where T : IGaeaSon
        {
            return dbConnection.Execute(commandText, sons);
        }

        /// <summary>
        /// 批量数据更新操作
        /// </summary>
        /// <typeparam name="T">映射类型</typeparam>
        /// <param name="commandText">T-SQL</param>
        /// <param name="sons">实例集合</param>
        /// <returns>影响行数</returns>
        public virtual int BatchUpdate<T>(string commandText, IEnumerable<T> sons) where T : IGaeaSon
        {
            using (var dbConnection = CreateConnection())
            {
                var result = dbConnection.Execute(commandText, sons);
                dbConnection.Close();
                return result;
            }
        }

        /// <summary>
        /// 批量数据更新操作
        /// </summary>
        /// <typeparam name="T">映射类型</typeparam>
        /// <param name="commandText">T-SQL</param>
        /// <param name="sons">实例</param>
        /// <returns>影响行数</returns>
        public virtual int Delete<T>(T son) where T : class, IGaeaSon
        {
            using (var dbConnection = CreateConnection())
            {
                var result = Delete(dbConnection, son);
                dbConnection.Close();
                return result;
            }
        }

        /// <summary>
        /// 删除数据操作 多用于事物
        /// </summary>
        /// <typeparam name="T">映射类型</typeparam>
        /// <param name="dbConnection">T-SQL</param>
        /// <param name="son">实例</param>
        /// <returns></returns>
        public virtual int Delete<T>(IDbConnection dbConnection, T son) where T : class, IGaeaSon
        {
            return dbConnection.Delete(this, son, null);
        }

        /// <summary>
        /// 删除所有数据 请谨慎使用
        /// </summary>
        /// <typeparam name="T">映射类型</typeparam>
        /// <returns>影响行数</returns>
        public virtual int DeleteAll<T>() where T : class, IGaeaSon
        {
            using (var dbConnection = CreateConnection())
            {
                var result = dbConnection.DeleteAll<T>(this);
                dbConnection.Close();
                return result;
            }
        }

        /// <summary>
        /// 如果GaeaSon实例Id 大于零Update 为零则Insert
        /// </summary>
        /// <typeparam name="T">映射类型</typeparam>
        /// <param name="son">实例</param>
        /// <returns>影响行数</returns>
        public virtual int Modify<T>(T son) where T : class, IGaeaSon
        {
            if (son.Id <= 0)
            {
                return Insert(son);
            }
            else
            {
                return Update(son);
            }
        }

        /// <summary>
        /// 如果GaeaSon实例Id 大于零Update 为零则Insert 多用于事物
        /// </summary>
        /// <typeparam name="T">映射类型</typeparam>
        /// <param name="dbConnection">Db Connection</param>
        /// <param name="son">实例</param>
        /// <returns>影响行数</returns>
        public virtual int Modify<T>(IDbConnection dbConnection, T son) where T : class, IGaeaSon
        {
            if (son.Id <= 0)
            {
                return Insert(dbConnection, son);
            }
            else
            {
                return Update(dbConnection, son);
            }
        }

        /// <summary>
        /// 两组返回数据结果匹配 
        /// </summary>
        /// <typeparam name="TFirst">第一组类型映射</typeparam>
        /// <typeparam name="TSecond">第二组类型映射</typeparam>
        /// <param name="commandText">T-SQL</param>
        /// <param name="parameters">具体参数</param>
        /// <returns>两组结果集合</returns>
        public virtual Tuple<IEnumerable<TFirst>, IEnumerable<TSecond>> FindMultiple<TFirst, TSecond>(string commandText, object parameters) where TFirst : class
        {
            using (var dbConnection = CreateConnection())
            {
                var multipleResult = dbConnection.QueryMultiple(commandText, parameters);

                var result = new Tuple<IEnumerable<TFirst>, IEnumerable<TSecond>>(
                     multipleResult.Read<TFirst>(), multipleResult.Read<TSecond>());

                dbConnection.Close();
                return result;
            }
        }
    }
}
